package server;

import java.io.File;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.nio.channels.FileChannel;
import java.nio.channels.NonWritableChannelException;
import java.nio.channels.OverlappingFileLockException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import model.BuyOrder;
import model.Order;
import model.SellOrder;
import model.Stock;
import model.User;

/**
 * Object for interacting with the Database.
 */
public class DataOperator {
	public String databaseName;
	private Connection conn;
	
	/**
	 * Sets up a DB using a given string as the file name
	 * 
	 * @param dbName
	 *            The name to store the db file as
	 * @throws SQLException
	 *             Unable to create connection
	 * @throws ClassNotFoundException
	 *             JDBC driver is missing
	 */
	public DataOperator(String dbName) throws SQLException,
			ClassNotFoundException {
		this.databaseName = dbName;

		// For JDBC driver
		Class.forName("org.sqlite.JDBC");

		// create a new connection
		conn = DriverManager.getConnection("jdbc:sqlite:" + dbName);
		
		boolean init = tableExists("ValidClients");
		if (!init) {
			initialiseTables();
		}
	}

	/**
	 * Creates the DB schema. Should only ever be called on first run of app.
	 * 
	 * @throws SQLException
	 */
	public void initialiseTables() throws SQLException {
		executeStatement("CREATE TABLE IF NOT EXISTS ValidClients"
				+ " (id INTEGER PRIMARY KEY, username UNIQUE, password);");
		
		executeStatement("CREATE TABLE IF NOT EXISTS Stocks"
				+ " (id INTEGER PRIMARY KEY, name UNIQUE, price);");
		
		executeStatement("CREATE TABLE IF NOT EXISTS Holdings"
				+ " (id INTEGER PRIMARY KEY, stock_id, account_id, number);");
		
		executeStatement("CREATE TABLE IF NOT EXISTS CompletedTransactions" 
				+ " (id INTEGER PRIMARY KEY, tid UNIQUE, user_id, date, stockname, number, price, isBuy);");
		
		executeStatement("INSERT INTO Stocks(name, price) VALUES('OSX', 250)");
		executeStatement("INSERT INTO Stocks(name, price) VALUES('Ria', 340)");
		executeStatement("INSERT INTO Stocks(name, price) VALUES('NIBC', 290)");
		executeStatement("INSERT INTO Stocks(name, price) VALUES('Vienta', 170)");
		executeStatement("INSERT INTO Stocks(name, price) VALUES('BHT', 260)");
		executeStatement("INSERT INTO Stocks(name, price) VALUES('Cones', 470)");
		
		// initial users
		executeStatement("INSERT INTO ValidClients(username, password) VALUES('a', 'a');");
		executeStatement("INSERT INTO ValidClients(username, password) VALUES('b', 'b');");
		executeStatement("INSERT INTO ValidClients(username, password) VALUES('c', 'c');");
		executeStatement("INSERT INTO ValidClients(username, password) VALUES('d', 'd');");
		
		// initial holdings, 2500 of each Stock to each User 
		PreparedStatement p = conn.prepareStatement(
				"INSERT INTO Holdings(stock_id, account_id, number) " +
				"VALUES(?, ?, 2500);");
		for(int i = 1; i <= 6; i++) {
			for(int j = 1; j <= 4; j++) {
				p.setInt(1, i);
				p.setInt(2, j);
				p.addBatch();
			}
		}
		conn.setAutoCommit(false);
		p.executeBatch();
		conn.setAutoCommit(true);
	}

	/**
	 * Executes a custom SQL query on the DB<br />
	 * 
	 * @param statement
	 *            Query to be executed
	 * @throws SQLException
	 *             Invalid query
	 */
	public void executeStatement(String statement) throws SQLException {
		Statement stat = conn.createStatement();
		stat.executeUpdate(statement);
	}

	/**
	 * Closes the database connection - should be done on app finish
	 * 
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		// close connection
		conn.close();
	}

	/**
	 * Save the state of the Database
	 * 
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		conn.commit();
	}

	/**
	 * Checks if a table with a given name exists in DB
	 * 
	 * @param table
	 *            Name of table to check existence of
	 * @return boolean representing whether the table exists
	 */
	public boolean tableExists(String table) {
		try {
			ResultSet rs = getResultSet("SELECT 1 FROM " + table + " WHERE 1=0");
			boolean b = rs.isClosed();
			rs.close();
			return b;
		} catch (SQLException e) {
			return false;
		}
	}

	/**
	 * Shorthand way to grab ResultSets
	 * 
	 * @param query
	 *            Query to be executed
	 * @return ResultSet of results
	 * @throws SQLException
	 */
	private ResultSet getResultSet(String query) throws SQLException {
		Statement stat = conn.createStatement();
		return stat.executeQuery(query);
	}

	/**
	 * Returns if the database is in use - useful for detecting if the
	 * application is already running
	 * 
	 * @return True if the database is in use, false otherwise
	 * @throws IOException
	 *             If closing access to the db file fails
	 */
	public boolean dbLocked() throws IOException {
		FileChannel fc = null;
		try {
			File f = new File(databaseName);
			fc = new RandomAccessFile(f, "rw").getChannel();
			return (fc.tryLock() == null) ? true : false;
		} catch (NonWritableChannelException e) {
			return true;
		} catch (OverlappingFileLockException e) {
			return true;
		} catch (Exception e) {
			return false;
		} finally {
			if (fc != null)
				fc.close();
		}
	}
	
	/** Add a new user to the database */
	public int registerUser(String username, String password)
			throws SQLException {
		PreparedStatement p = conn.prepareStatement(
			"INSERT INTO ValidClients(username, password) VALUES (?, ?)");
		int i = 1;
		p.setString(i++, username);
		p.setString(i++, password);
		p.execute();
		int id = p.getGeneratedKeys().getInt(1);
		return id;
	}

	/** Return all stocks in the database */
	public Map<String, Stock> getStocks() throws SQLException {
		PreparedStatement p = conn.prepareStatement("SELECT * FROM Stocks");
		Map<String, Stock> stocks = new HashMap<String, Stock>();
		ResultSet rs = p.executeQuery();
		while(rs.next()) {
			// id name price avail
			String stockname = rs.getString(2);
			stocks.put(stockname,
					new Stock(rs.getInt(1), stockname, rs.getInt(3)));
		}
		rs.close();
		return stocks;
	}

	/** Save details of a completed order to the database */
	public void completeTrade(Order o) throws SQLException {
		PreparedStatement p = conn.prepareStatement(
				"INSERT INTO CompletedTransactions(tid, user_id, date, stockname, " +
				"number, price, isBuy) VALUES (?, ?, ?, ?, ?, ?, ?)");
		int i = 1;
		p.setString(i++, o.getTransactionId());
		p.setInt(i++, o.getUserId());
		// convert util.Date to sql.Date
		p.setDate(i++, new Date(o.getDate().getTime()));
		p.setString(i++, o.getStockName());
		p.setInt(i++, o.getNumber());
		p.setInt(i++, o.getPrice());
		p.setBoolean(i++, (o instanceof BuyOrder));
		p.execute();
	}

	/** Return all completed orders by a user between given dates */
	public ArrayList<Order> getUserHistory(int userId, java.util.Date a, java.util.Date b) throws SQLException {
		ArrayList<Order> orders = new ArrayList<Order>();
		PreparedStatement p = conn.prepareStatement("SELECT user_id, tid, date, stockname, " +
				"number, price, isBuy FROM CompletedTransactions WHERE date >= ? AND date <= ? AND user_id = ?;");
		// convert util.Date to sql.Date
		p.setDate(1, new Date(a.getTime()));
		p.setDate(2, new Date(b.getTime()));
		p.setInt(3, userId);
		ResultSet r = p.executeQuery();
		while(r.next()) {
			boolean isBuy = r.getBoolean(7);
			Order o;
			if(isBuy) {
				o = new BuyOrder(r.getInt(1), r.getString(2), r.getDate(3),
						r.getString(4), r.getInt(5), r.getInt(6));
			} else {
				o = new SellOrder(r.getInt(1), r.getString(2), r.getDate(3),
						r.getString(4), r.getInt(5), r.getInt(6));
			}
			orders.add(o);
		}
		r.close();
		return orders;
	}
	
	/** Return the highest stored transaction ID */
	public int getMaxTID() throws SQLException {
		PreparedStatement p = conn
				.prepareStatement("SELECT MAX(id) FROM CompletedTransactions;");
		ResultSet r = p.executeQuery();
		return r.getInt(1);
	}

	/** Return all users in the database */
	public Map<Integer, User> getUsers() throws SQLException {
		PreparedStatement p = conn.prepareStatement("SELECT * FROM ValidClients");
		Map<Integer, User> users = new HashMap<Integer, User>();
		ResultSet r = p.executeQuery();
		while(r.next()) {
			int id = r.getInt(1);
			User u = new User(id, r.getString(2), r.getString(3));
			users.put(id, u);
		}
		r.close();
		return users;
	}

	/** Override users table with given user list */
	public void saveUsers(Collection<User> users) throws SQLException {
		PreparedStatement p = conn.prepareStatement(
				"REPLACE INTO ValidClients(id, username, password) VALUES(?, ?, ?);");
		for (User u : users) {
			p.setInt(1, u.getId());
			p.setString(2, u.getUsername());
			p.setString(3, u.getPassword());
//			p.addBatch();
			p.execute();
		}
//		conn.setAutoCommit(false);
//		p.executeBatch();
//		conn.setAutoCommit(true);
	}
	
	/** Return map of each users stock holdings */
	public Map<Integer, Map<String, Integer>> getHoldings() throws SQLException {
		Map<Integer, Map<String, Integer>> holdings = new HashMap<Integer, Map<String, Integer>>();
		PreparedStatement p = conn.prepareStatement(
				"SELECT Holdings.account_id, Holdings.number, Stocks.name " +
				"FROM Holdings, Stocks WHERE Holdings.stock_id = Stocks.id;");
		ResultSet r = p.executeQuery();
		int userId, number;
		String stockName;
		while(r.next()) {
			// id, stock id, acct id, number
			userId = r.getInt(1);
			number = r.getInt(2);
			stockName = r.getString(3);
			if(holdings.get(userId) == null) {
				holdings.put(userId, new HashMap<String, Integer>());
			}
			holdings.get(userId).put(stockName, number);
		}
		r.close();
		return holdings;
	}
	
	/** Update each users's stock holdings */
	public void saveHoldings(Map<String, Stock> stocks,
			Map<Integer, Map<String, Integer>> holdings) throws SQLException {
		executeStatement("DELETE FROM Holdings;");
		PreparedStatement p = conn.prepareStatement(
				"INSERT INTO Holdings(stock_id, account_id, number) " +
				"VALUES(?, ?, ?);");
		for (Integer accountId : holdings.keySet()) {
			Map<String, Integer> m = holdings.get(accountId);
			for (String stockname : m.keySet()) {
				int stockId = stocks.get(stockname).getId();
				p.setInt(1, stockId);
				p.setInt(2, accountId);
				p.setInt(3, m.get(stockname));
				p.execute();
//				p.addBatch();
			}
		}
//		conn.setAutoCommit(false);
//		p.executeBatch();
//		conn.setAutoCommit(true);
	}

	/** Update stored stock details */
	public void saveStocks(Collection<Stock> stocks) throws SQLException {
		PreparedStatement p = conn.prepareStatement(
				"REPLACE INTO Stocks(id, name, price) VALUES(?, ?, ?);");
		for (Stock s : stocks) {
			p.setInt(1, s.getId());
			p.setString(2, s.getName());
			p.setInt(3, s.getPrice());
//			p.addBatch();
			p.execute();
		}
//		conn.setAutoCommit(false);
//		p.executeBatch();
//		conn.setAutoCommit(true);
	}

}